/*MyPhotoIndex Free Photo Organizer <www.myphotoindex.com>
  Copyright (C) <2006-2009>  <Asaf Yarkoni asaf@yarkoni.net>

  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program.  If not, see <http://www.gnu.org/licenses/>.*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MyPhotoIndex.Utilities;
using MyPhotoIndex.Plugins.DataProviders;
using System.IO;

namespace MyPhotoIndex.DataAccess
{
    [Serializable]
    public class MSAccessDataProvider : ITagsDataAccessProvider,
                                        IImagesDataAccessProvider,
                                        IImageCategoriesDataAccessProvider,
                                        ISavedQueriesDataAccessProvider
    {
        private const string s_SelectDataNoParams = "select * from Images order by datetaken desc";
        private static MyPhotoIndexDataSet.ImagesDataTable ms_ImagesdataTable = new MyPhotoIndexDataSet.ImagesDataTable();

        private string m_dbPath;
        private OleDbConnection m_dbConnection;
        private DataTable m_tablesSchema;

        public bool Connect(string dbPath)
        {
            if (File.Exists(dbPath) == false)
            {
                return false;
            }

            m_dbPath = dbPath;
            string connectionStr = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;data source=", m_dbPath);

            if (m_dbConnection != null)
            {
                m_dbConnection.Dispose();
            }

            m_dbConnection = new OleDbConnection(connectionStr);
            m_dbConnection.Open();

            ValidateDb();

            return true;
        }

        private void ValidateDb()
        {
            try
            {
                if (IsTableExists("IndexInfo") == false)
                {
                    OleDbCommand command = new OleDbCommand("Create Table IndexInfo(DbVersion numeric, IndexId Text(32))", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("IndexInfo", "IndexName") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table IndexInfo Add IndexName Text(100)", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                //if (IsColumnExists("Images", "FileSignature") == false)
                //{
                //    OleDbCommand command = new OleDbCommand("Alter Table Images Add FileSignature Text(32)", m_dbConnection);
                //    int count = command.ExecuteNonQuery();
                //}

                if (IsColumnExists("ImageTags", "Keyword") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table ImageTags Add Keyword Text(100) UNIQUE INDEX", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("ImageTags", "PhysicalFolder") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table ImageTags Add PhysicalFolder Memo", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("ImageTags", "ReferenceDate") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table ImageTags Add ReferenceDate DateTime", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("Images", "Categories") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table Images Add Categories long integer", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("Images", "ParentId") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table Images Add ParentId long integer", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("Images", "VersionDate") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table Images Add VersionDate DateTime", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsColumnExists("Images", "SourceLabel") == false)
                {
                    OleDbCommand command = new OleDbCommand("Alter Table Images Add SourceLabel Text(255)", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsTableExists("ImageCategories") == false)
                {
                    OleDbCommand command = new OleDbCommand("Create Table ImageCategories(Id integer Primary Key, Description Text(255), Color integer)", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }

                if (IsTableExists("SavedQueries") == false)
                {
                    OleDbCommand command = new OleDbCommand("Create Table SavedQueries(Id Counter Primary Key, QueryData Memo)", m_dbConnection);
                    int count = command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(ex);
            }
        }

        public int GetDbVersionNumber()
        {
            OleDbCommand command = new OleDbCommand("Select DbVersion from IndexInfo", m_dbConnection);
            Object value = command.ExecuteScalar();
            if (value == null)
            {
                return 0;
            }

            return Convert.ToInt32(value);
        }

        public string GetDbIndexId()
        {
            OleDbCommand command = new OleDbCommand("Select Top 1 IndexId from IndexInfo", m_dbConnection);
            Object value = command.ExecuteScalar();
            if (value == null ||
                value == DBNull.Value)
            {
                command = new OleDbCommand("Select Count(*) from IndexInfo", m_dbConnection);
                int count = (int)command.ExecuteScalar();

                Guid guid = Guid.NewGuid();
                String indexId = guid.ToString().Substring(0, 32);

                if (count == 0)
                {
                    command = new OleDbCommand("Insert into IndexInfo(IndexId) values(@indexId)", m_dbConnection);
                    command.Parameters.Add(new OleDbParameter("param1", indexId.ToString()));
                    count = command.ExecuteNonQuery();
                }
                else
                {
                    command = new OleDbCommand("Update IndexInfo set IndexId = @indexId", m_dbConnection);
                    command.Parameters.Add(new OleDbParameter("param1", indexId));
                    count = command.ExecuteNonQuery();
                }

                return indexId;
            }

            return Convert.ToString(value);
        }

        public int GetImageListSize()
        {
            OleDbCommand command = new OleDbCommand("Select Count('A') from Images", m_dbConnection);
            return (int)command.ExecuteScalar();
        }

        public string GetDbIndexName()
        {
            OleDbCommand command = new OleDbCommand("Select Top 1 IndexName from IndexInfo", m_dbConnection);
            Object value = command.ExecuteScalar();
            if (value == null ||
                value == DBNull.Value)
            {
                string indexName = "Default index";
                command = new OleDbCommand("Update IndexInfo set IndexName = @indexName", m_dbConnection);
                command.Parameters.Add(new OleDbParameter("param1", indexName));
                int count = command.ExecuteNonQuery();

                return indexName;
            }

            return Convert.ToString(value);
        }

        public void SetDbVersionNumber(int newVersion)
        {
            OleDbCommand command = new OleDbCommand("Select Count(*) from IndexInfo", m_dbConnection);
            int count = (int)command.ExecuteScalar();

            if (count == 0)
            {
                command = new OleDbCommand("Insert into IndexInfo(DbVersion) values(@version)", m_dbConnection);
                command.Parameters.Add(new OleDbParameter("param1", newVersion));
                count = command.ExecuteNonQuery();
            }
            else
            {
                command = new OleDbCommand("Update IndexInfo set DbVersion = @version", m_dbConnection);
                command.Parameters.Add(new OleDbParameter("param1", newVersion));
                count = command.ExecuteNonQuery();
            }
        }

        private bool IsColumnExists(string tableName, string columnName)
        {
            DataTable schema = m_dbConnection.GetSchema("Columns");
            DataTableReader reader = schema.CreateDataReader();

            foreach (System.Data.Common.DbDataRecord row in reader)
            {
                String table = row["TABLE_NAME"] as String;
                String column = row["COLUMN_NAME"] as String;

                if (String.Compare(table, tableName, true) == 0 &&
                   String.Compare(column, columnName, true) == 0)
                {
                    return true;
                }
            }

            return false;
        }

        private bool IsTableExists(string tableName)
        {
            if (m_tablesSchema == null)
            {
                m_tablesSchema = m_dbConnection.GetSchema("Tables");
            }

            DataTableReader reader = m_tablesSchema.CreateDataReader();

            foreach (System.Data.Common.DbDataRecord row in reader)
            {
                String table = row["TABLE_NAME"] as String;

                if (String.Compare(table, tableName, true) == 0)
                {
                    return true;
                }
            }

            return false;
        }

        public Object GetColumnValue(int imageId, ImageDataSourceColumnId imageDataSourceColumnId)
        {
            MyPhotoIndexDataSet.ImagesDataTable dataTable = new MyPhotoIndexDataSet.ImagesDataTable();
            using (OleDbCommand command = new OleDbCommand("Select " + imageDataSourceColumnId.ToString() + " From Images Where Id = ?", m_dbConnection))
            {
                command.Parameters.Add(new OleDbParameter("param1", imageId));
                return command.ExecuteScalar();
            }
        }

        public OleDbDataReader GetPhotoIndexData()
        {
            OleDbDataReader reader = null;
            using (OleDbCommand command = new OleDbCommand(s_SelectDataNoParams, m_dbConnection))
            {
                reader = command.ExecuteReader();
            }

            return reader;
        }

        public void UpdateImageData(int imageId, ImageDataSourceColumnId dataColumn, object data)
        {
            ImageDataSourceColumnId[] dataColumns = new ImageDataSourceColumnId[] { dataColumn };
            Object[] dataArray = new Object[] { data };

            UpdateImageData(imageId, dataColumns, dataArray);
        }

        public void UpdateImageData(int imageId, ImageDataSourceColumnId[] dataColumns, object[] data)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update Images set ");

            int last = dataColumns.Length;
            foreach (ImageDataSourceColumnId dataColumn in dataColumns)
            {
                string dataColumnName = ms_ImagesdataTable.Columns[(int)dataColumn].ColumnName;
                sb.Append(String.Concat(dataColumnName, " = ?"));

                if (--last > 0)
                {
                    sb.Append(", ");
                }
                else
                {
                    sb.Append(" ");
                }
            }

            sb.Append(" where Id = ?");

            String queryString = sb.ToString();
            using (OleDbCommand command = new OleDbCommand(queryString, m_dbConnection))
            {
                int index = 0;
                foreach (Object dataElement in data)
                {
                    String paramName = String.Concat("param", index);
                    if (dataElement != null)
                    {
                        command.Parameters.Add(new OleDbParameter(paramName, dataElement));
                    }
                    else
                    {
                        command.Parameters.Add(new OleDbParameter(paramName, DBNull.Value));
                    }

                    if (dataElement is DateTime)
                    {
                        command.Parameters[index].OleDbType = OleDbType.Date;
                    }

                    ++index;
                }

                command.Parameters.Add(new OleDbParameter("paramCond", imageId));

                int affected = command.ExecuteNonQuery();
            }
        }

        public DataTable GetImageTags()
        {
            MyPhotoIndexDataSet.ImageTagsDataTable dataTable = new MyPhotoIndexDataSet.ImageTagsDataTable();
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from ImageTags order by Parent, offset", m_dbConnection))
            {
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
        }

        public void UpdateDataTable(DataTable dataTable, bool continueOnError)
        {
            UpdateDataTable(dataTable, continueOnError, false);
        }

        public void UpdateDataTable(DataTable dataTable, bool continueOnError, bool updateIdentity)
        {
            if (dataTable == null)
            {
                return;
            }

            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select Top 1 * from " + dataTable.TableName, m_dbConnection))
            {
                using (OleDbCommandBuilder builder = new OleDbCommandBuilder(dataAdapter))
                {
                    if (updateIdentity == true)
                    {
                        dataAdapter.RowUpdated += new OleDbRowUpdatedEventHandler(dataAdapter_RowUpdated);
                    }
                    dataAdapter.ContinueUpdateOnError = continueOnError;
                    int affected = dataAdapter.Update(dataTable);
                }
            }
        }

        void dataAdapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
        {
            if (e.Errors != null)
            {
                Logger.Log(e.Errors);
            }

            int newID = 0;
            OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", m_dbConnection);
            if (e.StatementType == StatementType.Insert)
            {
                // Retrieve the identity value and store it in the CategoryID column.
                newID = (int)idCMD.ExecuteScalar();
                DataColumn identityCol = null;
                foreach (DataColumn col in e.Row.Table.Columns)
                {
                    if (col.AutoIncrement == true)
                    {
                        identityCol = col;
                        break;
                    }
                }

                if (identityCol != null)
                {
                    e.Row[identityCol.Ordinal] = newID;
                }
            }
        }

        public void RemoveImage(int imageId)
        {
            using (OleDbCommand command = new OleDbCommand("delete from Images where Id = ?", m_dbConnection))
            {
                command.Parameters.Add(new OleDbParameter("param1", imageId));

                command.ExecuteNonQuery();
            }
        }

        #region IDisposable Members

        ~MSAccessDataProvider()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing == true &&
                m_dbConnection != null)
            {
                m_dbConnection.Close();
                m_dbConnection.Dispose();
            }
        }

        #endregion

        #region IImageCategoriesDataAccessProvider Members

        public DataTable GetImageCategories()
        {
            MyPhotoIndexDataSet.ImageCategoriesDataTable dataTable = new MyPhotoIndexDataSet.ImageCategoriesDataTable();
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from ImageCategories order by Id", m_dbConnection))
            {
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
        }

        #endregion

        #region ISavedQueriesDataAccessProvider Members

        public DataTable GetSavedQueries()
        {
            MyPhotoIndexDataSet.SavedQueriesDataTable dataTable = new MyPhotoIndexDataSet.SavedQueriesDataTable();
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from SavedQueries order by Id", m_dbConnection))
            {
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
        }

        #endregion
    }
}